using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    /// <summary> 
    ///Author: nnamthach@gmail.com 
    /// <summary>
    public class ProductsDataAccess : Connection
    {
        #region Local Variable
        private string _strSPInsertName = "dbo.[procProducts_add]";
        private string _strSPUpdateName = "dbo.[procProducts_update]";
        private string _strSPDeleteName = "dbo.[procProducts_delete]";
        private string _strSPGetName = "dbo.[procProducts_get]";
        private string _strSPGetAllName = "dbo.[procProducts_getall]";
        private string _strSPGetCateName = "dbo.[procProducts_getcate]";
        private string _strSPGetArrCateName = "dbo.[procProducts_getcates]";
        string _strSPGetIDArrCateName = "dbo.[procProducts_getidbycates]";
        string _strSPGetInIDArrCateName = "dbo.[procProducts_getidinarraybycates]";
        string _strSPGetInIDName = "procProducts_getinid";
        private string _strSPGetPages = "dbo.[procProducts_getpaged]";
        private string _strSPIsExist = "dbo.[procProducts_isexist]";
        private string _strTableName = "[Products]";
        private string _strSPGetTransferOutName = "dbo.[procProducts_gettransferout]";
        private string _strSPGetName_IDCat = "dbo.[sp_Products_get]";
        private string _strSPGetTopSeller = "dbo.[procProducts_getTopSeller]";
        private string _strSPGetTopNew = "dbo.[procProducts_getTopNew]";
        private string _strSPGetAllIndex = "dbo.[procProducts_getallIndex]";
        #endregion Local Variable

        #region Method
        public ProductsInfo Get(
        Int64 ID,
        ref string sErr)
        {
            ProductsInfo objEntr = new ProductsInfo();
            connect();
            InitSPCommand(_strSPGetName);
            AddParameter(ProductsInfo.Field.ID.ToString(), ID);

            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP(command);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();

            if (list.Rows.Count > 0)
                objEntr = (ProductsInfo)GetDataFromDataRow(list, 0);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }

        protected override object GetDataFromDataRow(DataTable dt, int i)
        {
            ProductsInfo result = new ProductsInfo();
            result.ID = (dt.Rows[i][ProductsInfo.Field.ID.ToString()] == DBNull.Value ? 0 : Convert.ToInt64(dt.Rows[i][ProductsInfo.Field.ID.ToString()]));
            result.Code = (dt.Rows[i][ProductsInfo.Field.Code.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.Code.ToString()]));
            result.Skue = (dt.Rows[i][ProductsInfo.Field.Skue.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.Skue.ToString()]));
            result.IDCategory = (dt.Rows[i][ProductsInfo.Field.IDCategory.ToString()] == DBNull.Value ? 0 : Convert.ToInt32(dt.Rows[i][ProductsInfo.Field.IDCategory.ToString()]));
            result.Name = (dt.Rows[i][ProductsInfo.Field.Name.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.Name.ToString()]));
            result.NameVN = (dt.Rows[i][ProductsInfo.Field.NameVN.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.NameVN.ToString()]));
            result.ShortDecribes = (dt.Rows[i][ProductsInfo.Field.ShortDecribes.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.ShortDecribes.ToString()]));
            result.LongDecribes = (dt.Rows[i][ProductsInfo.Field.LongDecribes.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.LongDecribes.ToString()]));
            result.ShortDecribesVN = (dt.Rows[i][ProductsInfo.Field.ShortDecribesVN.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.ShortDecribesVN.ToString()]));
            result.LongDecribesVN = (dt.Rows[i][ProductsInfo.Field.LongDecribesVN.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.LongDecribesVN.ToString()]));
            result.Image = (dt.Rows[i][ProductsInfo.Field.Image.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.Image.ToString()]));
            result.CountPreview = (dt.Rows[i][ProductsInfo.Field.CountPreview.ToString()] == DBNull.Value ? 0 : Convert.ToInt64(dt.Rows[i][ProductsInfo.Field.CountPreview.ToString()]));
            result.Icon = (dt.Rows[i][ProductsInfo.Field.Icon.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.Icon.ToString()]));
            result.Status = (dt.Rows[i][ProductsInfo.Field.Status.ToString()] == DBNull.Value ? 0 : Convert.ToInt32(dt.Rows[i][ProductsInfo.Field.Status.ToString()]));
            result.OldPrice = (dt.Rows[i][ProductsInfo.Field.OldPrice.ToString()] == DBNull.Value ? 0 : Convert.ToDouble(dt.Rows[i][ProductsInfo.Field.OldPrice.ToString()]));
            result.NewPrice = (dt.Rows[i][ProductsInfo.Field.NewPrice.ToString()] == DBNull.Value ? 0 : Convert.ToDouble(dt.Rows[i][ProductsInfo.Field.NewPrice.ToString()]));
            result.Warranty = (dt.Rows[i][ProductsInfo.Field.Warranty.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.Warranty.ToString()]));
            result.Promote = (dt.Rows[i][ProductsInfo.Field.Promote.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.Promote.ToString()]));
            result.Weight = (dt.Rows[i][ProductsInfo.Field.Weight.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.Weight.ToString()]));
            result.Dimension = (dt.Rows[i][ProductsInfo.Field.Dimension.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.Dimension.ToString()]));
            result.TypeView = (dt.Rows[i][ProductsInfo.Field.TypeView.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.TypeView.ToString()]));
            result.Quantity = (dt.Rows[i][ProductsInfo.Field.Quantity.ToString()] == DBNull.Value ? 0 : Convert.ToInt32(dt.Rows[i][ProductsInfo.Field.Quantity.ToString()]));
            result.WarrantyVN = (dt.Rows[i][ProductsInfo.Field.WarrantyVN.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.WarrantyVN.ToString()]));
            result.PromoteVN = (dt.Rows[i][ProductsInfo.Field.PromoteVN.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.PromoteVN.ToString()]));
            result.IDImage = (dt.Rows[i][ProductsInfo.Field.IDImage.ToString()] == DBNull.Value ? 0 : Convert.ToInt32(dt.Rows[i][ProductsInfo.Field.IDImage.ToString()]));
            result.ANAL_P0 = (dt.Rows[i][ProductsInfo.Field.ANAL_P0.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.ANAL_P0.ToString()]));
            result.ANAL_P1 = (dt.Rows[i][ProductsInfo.Field.ANAL_P1.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.ANAL_P1.ToString()]));
            result.ANAL_P2 = (dt.Rows[i][ProductsInfo.Field.ANAL_P2.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.ANAL_P2.ToString()]));
            result.ANAL_P3 = (dt.Rows[i][ProductsInfo.Field.ANAL_P3.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.ANAL_P3.ToString()]));
            result.ANAL_P4 = (dt.Rows[i][ProductsInfo.Field.ANAL_P4.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.ANAL_P4.ToString()]));
            result.ANAL_P5 = (dt.Rows[i][ProductsInfo.Field.ANAL_P5.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.ANAL_P5.ToString()]));
            result.ANAL_P6 = (dt.Rows[i][ProductsInfo.Field.ANAL_P6.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.ANAL_P6.ToString()]));
            result.ANAL_P7 = (dt.Rows[i][ProductsInfo.Field.ANAL_P7.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.ANAL_P7.ToString()]));
            result.ANAL_P8 = (dt.Rows[i][ProductsInfo.Field.ANAL_P8.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.ANAL_P8.ToString()]));
            result.ANAL_P9 = (dt.Rows[i][ProductsInfo.Field.ANAL_P9.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.ANAL_P9.ToString()]));
            result.BEN_H = (dt.Rows[i][ProductsInfo.Field.BEN_H.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.BEN_H.ToString()]));
            result.BEN_S = (dt.Rows[i][ProductsInfo.Field.BEN_S.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.BEN_S.ToString()]));
            result.BEN_C = (dt.Rows[i][ProductsInfo.Field.BEN_C.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.BEN_C.ToString()]));
            result.BEN_R = (dt.Rows[i][ProductsInfo.Field.BEN_R.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.BEN_R.ToString()]));
            result.BEN_F = (dt.Rows[i][ProductsInfo.Field.BEN_F.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.BEN_F.ToString()]));
            result.Rate = (dt.Rows[i][ProductsInfo.Field.Rate.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.Rate.ToString()]));
            result.Brand = (dt.Rows[i][ProductsInfo.Field.Brand.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.Brand.ToString()]));
            result.Material = (dt.Rows[i][ProductsInfo.Field.Material.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.Material.ToString()]));
            result.Age = (dt.Rows[i][ProductsInfo.Field.Age.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.Age.ToString()]));
            result.Usage = (dt.Rows[i][ProductsInfo.Field.Usage.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.Usage.ToString()]));
            result.Occasion = (dt.Rows[i][ProductsInfo.Field.Occasion.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][ProductsInfo.Field.Occasion.ToString()]));

            return result;
        }

        public DataTable GetAll(
        ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetAllName);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetByPos(
        int pos, ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetAllName);
            AddParameter("INX", pos);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public int GetCountRecord(
        ref string sErr)
        {
            int ret = -1;
            connect();
            InitSPCommand(_strSPInsertName);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeSPScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }
        /// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add(ProductsInfo objEntr, ref string sErr)
        {
            int ret = -1;
            connect();
            InitSPCommand(_strSPInsertName);
            AddParameter(ProductsInfo.Field.Code.ToString(), objEntr.Code);
            AddParameter(ProductsInfo.Field.Skue.ToString(), objEntr.Skue);
            AddParameter(ProductsInfo.Field.IDCategory.ToString(), objEntr.IDCategory);
            AddParameter(ProductsInfo.Field.Name.ToString(), objEntr.Name);
            AddParameter(ProductsInfo.Field.NameVN.ToString(), objEntr.NameVN);
            AddParameter(ProductsInfo.Field.ShortDecribes.ToString(), objEntr.ShortDecribes);
            AddParameter(ProductsInfo.Field.LongDecribes.ToString(), objEntr.LongDecribes);
            AddParameter(ProductsInfo.Field.ShortDecribesVN.ToString(), objEntr.ShortDecribesVN);
            AddParameter(ProductsInfo.Field.LongDecribesVN.ToString(), objEntr.LongDecribesVN);
            AddParameter(ProductsInfo.Field.Image.ToString(), objEntr.Image);
            AddParameter(ProductsInfo.Field.CountPreview.ToString(), objEntr.CountPreview);
            AddParameter(ProductsInfo.Field.Icon.ToString(), objEntr.Icon);
            AddParameter(ProductsInfo.Field.Status.ToString(), objEntr.Status);
            AddParameter(ProductsInfo.Field.OldPrice.ToString(), objEntr.OldPrice);
            AddParameter(ProductsInfo.Field.NewPrice.ToString(), objEntr.NewPrice);
            AddParameter(ProductsInfo.Field.Warranty.ToString(), objEntr.Warranty);
            AddParameter(ProductsInfo.Field.Promote.ToString(), objEntr.Promote);
            AddParameter(ProductsInfo.Field.Weight.ToString(), objEntr.Weight);
            AddParameter(ProductsInfo.Field.Dimension.ToString(), objEntr.Dimension);
            AddParameter(ProductsInfo.Field.TypeView.ToString(), objEntr.TypeView);
            AddParameter(ProductsInfo.Field.Quantity.ToString(), objEntr.Quantity);
            AddParameter(ProductsInfo.Field.WarrantyVN.ToString(), objEntr.WarrantyVN);
            AddParameter(ProductsInfo.Field.PromoteVN.ToString(), objEntr.PromoteVN);
            AddParameter(ProductsInfo.Field.IDImage.ToString(), objEntr.IDImage);
            AddParameter(ProductsInfo.Field.ANAL_P0.ToString(), objEntr.ANAL_P0);
            AddParameter(ProductsInfo.Field.ANAL_P1.ToString(), objEntr.ANAL_P1);
            AddParameter(ProductsInfo.Field.ANAL_P2.ToString(), objEntr.ANAL_P2);
            AddParameter(ProductsInfo.Field.ANAL_P3.ToString(), objEntr.ANAL_P3);
            AddParameter(ProductsInfo.Field.ANAL_P4.ToString(), objEntr.ANAL_P4);
            AddParameter(ProductsInfo.Field.ANAL_P5.ToString(), objEntr.ANAL_P5);
            AddParameter(ProductsInfo.Field.ANAL_P6.ToString(), objEntr.ANAL_P6);
            AddParameter(ProductsInfo.Field.ANAL_P7.ToString(), objEntr.ANAL_P7);
            AddParameter(ProductsInfo.Field.ANAL_P8.ToString(), objEntr.ANAL_P8);
            AddParameter(ProductsInfo.Field.ANAL_P9.ToString(), objEntr.ANAL_P9);
            AddParameter(ProductsInfo.Field.BEN_H.ToString(), objEntr.BEN_H);
            AddParameter(ProductsInfo.Field.BEN_S.ToString(), objEntr.BEN_S);
            AddParameter(ProductsInfo.Field.BEN_C.ToString(), objEntr.BEN_C);
            AddParameter(ProductsInfo.Field.BEN_R.ToString(), objEntr.BEN_R);
            AddParameter(ProductsInfo.Field.BEN_F.ToString(), objEntr.BEN_F);
            AddParameter(ProductsInfo.Field.Rate.ToString(), objEntr.Rate);
            AddParameter(ProductsInfo.Field.Brand.ToString(), objEntr.Brand);
            AddParameter(ProductsInfo.Field.Material.ToString(), objEntr.Material);
            AddParameter(ProductsInfo.Field.Age.ToString(), objEntr.Age);
            AddParameter(ProductsInfo.Field.Usage.ToString(), objEntr.Usage);
            AddParameter(ProductsInfo.Field.Occasion.ToString(), objEntr.Occasion);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeSPScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public string Update(ProductsInfo objEntr)
        {
            connect();
            InitSPCommand(_strSPUpdateName);
            AddParameter(ProductsInfo.Field.ID.ToString(), objEntr.ID);
            AddParameter(ProductsInfo.Field.Code.ToString(), objEntr.Code);
            AddParameter(ProductsInfo.Field.Skue.ToString(), objEntr.Skue);
            AddParameter(ProductsInfo.Field.IDCategory.ToString(), objEntr.IDCategory);
            AddParameter(ProductsInfo.Field.Name.ToString(), objEntr.Name);
            AddParameter(ProductsInfo.Field.NameVN.ToString(), objEntr.NameVN);
            AddParameter(ProductsInfo.Field.ShortDecribes.ToString(), objEntr.ShortDecribes);
            AddParameter(ProductsInfo.Field.LongDecribes.ToString(), objEntr.LongDecribes);
            AddParameter(ProductsInfo.Field.ShortDecribesVN.ToString(), objEntr.ShortDecribesVN);
            AddParameter(ProductsInfo.Field.LongDecribesVN.ToString(), objEntr.LongDecribesVN);
            AddParameter(ProductsInfo.Field.Image.ToString(), objEntr.Image);
            AddParameter(ProductsInfo.Field.CountPreview.ToString(), objEntr.CountPreview);
            AddParameter(ProductsInfo.Field.Icon.ToString(), objEntr.Icon);
            AddParameter(ProductsInfo.Field.Status.ToString(), objEntr.Status);
            AddParameter(ProductsInfo.Field.OldPrice.ToString(), objEntr.OldPrice);
            AddParameter(ProductsInfo.Field.NewPrice.ToString(), objEntr.NewPrice);
            AddParameter(ProductsInfo.Field.Warranty.ToString(), objEntr.Warranty);
            AddParameter(ProductsInfo.Field.Promote.ToString(), objEntr.Promote);
            AddParameter(ProductsInfo.Field.Weight.ToString(), objEntr.Weight);
            AddParameter(ProductsInfo.Field.Dimension.ToString(), objEntr.Dimension);
            AddParameter(ProductsInfo.Field.TypeView.ToString(), objEntr.TypeView);
            AddParameter(ProductsInfo.Field.Quantity.ToString(), objEntr.Quantity);
            AddParameter(ProductsInfo.Field.WarrantyVN.ToString(), objEntr.WarrantyVN);
            AddParameter(ProductsInfo.Field.PromoteVN.ToString(), objEntr.PromoteVN);
            AddParameter(ProductsInfo.Field.IDImage.ToString(), objEntr.IDImage);
            AddParameter(ProductsInfo.Field.ANAL_P0.ToString(), objEntr.ANAL_P0);
            AddParameter(ProductsInfo.Field.ANAL_P1.ToString(), objEntr.ANAL_P1);
            AddParameter(ProductsInfo.Field.ANAL_P2.ToString(), objEntr.ANAL_P2);
            AddParameter(ProductsInfo.Field.ANAL_P3.ToString(), objEntr.ANAL_P3);
            AddParameter(ProductsInfo.Field.ANAL_P4.ToString(), objEntr.ANAL_P4);
            AddParameter(ProductsInfo.Field.ANAL_P5.ToString(), objEntr.ANAL_P5);
            AddParameter(ProductsInfo.Field.ANAL_P6.ToString(), objEntr.ANAL_P6);
            AddParameter(ProductsInfo.Field.ANAL_P7.ToString(), objEntr.ANAL_P7);
            AddParameter(ProductsInfo.Field.ANAL_P8.ToString(), objEntr.ANAL_P8);
            AddParameter(ProductsInfo.Field.ANAL_P9.ToString(), objEntr.ANAL_P9);
            AddParameter(ProductsInfo.Field.BEN_H.ToString(), objEntr.BEN_H);
            AddParameter(ProductsInfo.Field.BEN_S.ToString(), objEntr.BEN_S);
            AddParameter(ProductsInfo.Field.BEN_C.ToString(), objEntr.BEN_C);
            AddParameter(ProductsInfo.Field.BEN_R.ToString(), objEntr.BEN_R);
            AddParameter(ProductsInfo.Field.BEN_F.ToString(), objEntr.BEN_F);
            AddParameter(ProductsInfo.Field.Rate.ToString(), objEntr.Rate);
            AddParameter(ProductsInfo.Field.Brand.ToString(), objEntr.Brand);
            AddParameter(ProductsInfo.Field.Material.ToString(), objEntr.Material);
            AddParameter(ProductsInfo.Field.Age.ToString(), objEntr.Age);
            AddParameter(ProductsInfo.Field.Usage.ToString(), objEntr.Usage);
            AddParameter(ProductsInfo.Field.Occasion.ToString(), objEntr.Occasion);

            string sErr = "";
            try
            {
                excuteSPNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete(
        Int64 ID
        )
        {
            connect();
            InitSPCommand(_strSPDeleteName);
            AddParameter(ProductsInfo.Field.ID.ToString(), ID);

            string sErr = "";
            try
            {
                excuteSPNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public DataTableCollection Get_Page(ProductsInfo obj, string orderBy, int pageIndex, int pageSize, ref String sErr)
        {
            string whereClause = CreateWhereClause(obj);
            DataTableCollection dtList = null;
            connect();
            InitSPCommand(_strSPGetPages);

            AddParameter("WhereClause", whereClause);
            AddParameter("OrderBy", orderBy);
            AddParameter("PageIndex", pageIndex);
            AddParameter("PageSize", pageSize);

            try
            {
                dtList = executeCollectSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            return dtList;
        }

        public Boolean IsExist(
        Int64 ID
        )
        {
            connect();
            InitSPCommand(_strSPIsExist);
            AddParameter(ProductsInfo.Field.ID.ToString(), ID);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }

        private string CreateWhereClause(ProductsInfo obj)
        {
            String result = "";

            return result;
        }

        public DataTable Search(string columnName, string columnValue, string condition, string tableName, ref string sErr)
        {
            string query = "select * from " + _strTableName + " where " + columnName + " " + condition + " " + columnValue;
            DataTable list = new DataTable();
            connect();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetTransferOut(string dtb, object from, object to, ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetTransferOutName);
            AddParameter("DB", dtb);
            AddParameter("FROM", from);
            AddParameter("TO", to);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        #endregion Method

        public DataTable get_child(Int64 ID, ref string sErr)
        {
            ProductsInfo objEntr = new ProductsInfo();

            connect();
            string query = "exec " + _strSPGetName_IDCat + " " + ID;
            DataTable list = new DataTable(); ;
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            return list;
        }
        public DataTable get_allproduct(DataTable dt, ref string sErr)
        {
            ProductsInfo prdinfo = new ProductsInfo();
            DataTable rs = ProductsInfo.ToDataTable();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                Int32 iID = (Int32)dt.Rows[i]["ID"];
                DataTable temp = get_child(iID, ref sErr);
                rs = ImportTable(temp, rs);
            }
            return rs;
        }
        private DataTable ImportTable(DataTable dtSour, DataTable dtDes)
        {
            foreach (DataColumn x in dtSour.Columns)
            {
                if (!dtDes.Columns.Contains(x.ColumnName))
                    dtDes.Columns.Add(x.ColumnName, x.DataType);
            }
            for (int i = 0; i < dtSour.Rows.Count; i++)
            {
                dtDes.ImportRow(dtSour.Rows[i]);
            }
            return dtDes;
        }

        public DataTable GetByCate(int cate, ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetCateName);
            AddParameter("Cate", cate);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable GetTopSeller(ref string sErr)

        {
            connect();
            InitSPCommand(_strSPGetTopSeller);

            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();

            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
          public DataTable GetTopNew(ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetTopNew);

            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();

            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

          public DataTable GetAllIndex(ref string sErr)
          {
              connect();
              InitSPCommand(_strSPGetAllIndex);

              DataTable list = new DataTable();
              try
              {
                  list = executeSelectSP();
              }
              catch (Exception ex)
              {
                  sErr = ex.Message;
              }
              disconnect();

              if (sErr != "") ErrorLog.SetLog(sErr);
              return list;
          }
        public DataTable GetByArrCate(string xmlCate, string xmlPath, ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetArrCateName);
            AddParameter("XmlCate", xmlCate);
            AddParameter("XmlPath", xmlPath);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable GetIDByArrCate(string xmlCate, string xmlPath, ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetIDArrCateName);
            AddParameter("XmlCate", xmlCate);
            AddParameter("XmlPath", xmlPath);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable GetIDByArrCate(string xmlID, string xmlCate, string xmlPath, ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetInIDArrCateName);
            AddParameter("XmlCate", xmlCate);
            AddParameter("XmlID", xmlID);
            AddParameter("XmlPath", xmlPath);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable GetInID(string xmlID, string xmlPath, ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetInIDName);
            AddParameter("XmlID", xmlID);
            AddParameter("XmlPath", xmlPath);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
    }
}
